/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package BD.Modulos;
import BD.Bd;
import BD.ConnBdType;
import Interfaces.BdException;
import Interfaces.StaffInterface;
import Objects.Log_Personal;
import Objects.Log_TPersonal;
import WS.Staff;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author armgarces
 */
public class BdStaff extends Bd{
    private String mensj;
    /***************************************************************************/
    /********************************** BD *************************************/
    /***************************************************************************/
    /**
     * 
     */
    public BdStaff(){      
    }  
    /**
     * 
     * @param value
     * @return 
     */
    private String selectLoadSP(StaffInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Log_Personal":
                return "{call sp_get_log_personal_all}";
            case "Log_TPersonal":
                return "{call sp_get_log_tpersonal_all}";
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectFindSP(StaffInterface value,int type){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Log_Personal":
                switch(type){
                    case Staff.FIND_PERSONAL:
                        params = new String[]{"@find"};
                        return "{call sp_get_log_personal_find(?)}";
                    case Staff.FIND_PERSONAL_TP:
                        params = new String[]{"@find","@tpersonal1","@tpersonal2"};
                        return "{call sp_get_log_personal_find_tp(?,?,?)}"; 
                }
            case "Log_TPersonal":
                params = new String[]{"@find"};
                return "{call sp_get_log_tpersonal_find(?)}";
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectCheckSP(int value){
        switch(value){
            case Staff.CHECK_PERSONAL:
                params = new String[]{"@log_Personal"};
                return "{call sp_get_log_personal_check(?,?)}";
            case Staff.CHECK_TPERSONAL:
                params = new String[]{"@log_tpersonal"};
                return "{call sp_get_log_tpersonal_check(?,?)}";
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectChangeSP(StaffInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Log_Personal":
                mensj = "deletePersonal:";
                params = new String[]{"@nro_ci","@status"};
                return "{call sp_upd_log_personal_status(?,?)}";
            case "Log_TPersonal":
                mensj = "deleteTPersonal:";
                params = new String[]{"@id_tpersonal","@status"};
                return "{call sp_upd_log_tpersonal_status(?,?)}";
            default:
                return "";
        }
    }    
    /***************************************************************************/
    /********************************* STAFF ***********************************/
    /***************************************************************************/    
    /**
     * @author MITM
     * @param value
     * @return 
     * @throws java.sql.SQLException 
     * @throws Interfaces.BdException 
     */
    public StaffInterface[] load(StaffInterface value) throws SQLException, BdException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();            
            if (connection != null){                
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectLoadSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                ResultSet result = cstmt.executeQuery();            
                List<StaffInterface> list = new ArrayList<>();
                while(result.next()) {
                   StaffInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new StaffInterface[list.size()]);               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     *      
     * @param value
     * @param find
     * @param type define el tipo de busqueda FIND (solo usado para FIND_PERSONAL y FIND_PERSONAL_TP)
     * @param tp1 TPersonal #1 (usado solo en FIND_PERSONAL_TP)
     * @param tp2 TPersonal #2 (usado solo en FIND_PERSONAL_TP)     
     * @return 
     * @throws java.sql.SQLException 
     * @throws Interfaces.BdException 
     */
    public StaffInterface[] find(StaffInterface value,String find,int type,int tp1,int tp2) throws SQLException, BdException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectFindSP(value, type));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                cstmt.setString(params[0]  , find );
                if(type == Staff.FIND_PERSONAL_TP){
                    cstmt.setInt(params[1] ,tp1);
                    cstmt.setInt(params[2] ,tp2);
                }
                ResultSet result = cstmt.executeQuery();            
                List<StaffInterface> list = new ArrayList<>();
                while(result.next()) {
                   StaffInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new StaffInterface[list.size()]);               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }      
    /**
     * 
     * @param check
     * @param name valor a ser chequeado en la BD
     * @return 
     * @throws java.sql.SQLException
     * @throws Interfaces.BdException
     */
    public boolean check(int check,String name) throws SQLException, BdException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectCheckSP(check));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                           
                cstmt.setString(params[0], name);
                cstmt.registerOutParameter("result", java.sql.Types.INTEGER);               
                cstmt.execute();
                return cstmt.getInt("result")==1;
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * @param user
     * @param value
     * @return 
     * @throws Interfaces.BdException 
     * @throws java.sql.SQLException 
     */
    public boolean change(String user,StaffInterface value) throws BdException, SQLException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);            
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectChangeSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());                 
                cstmt.setObject(params[0], value.getId());
                int stat = 0;
                if(value.getStatus() == 0){
                    stat = 1;
                }            
                cstmt.setInt(params[1], stat );
                cstmt.execute();            
                auditar(user,value.getId()+"",mensj);
                connection.commit();
                return true; 
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }          
    /**
     * @author MITM
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param value
     * @return
     * @throws SQLException
     * @throws BdException 
     */
    public boolean savePersonal(String user, int operacion, Log_Personal value) throws SQLException, BdException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_log_personal_data(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        mensj = "Nuevo Personal:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_log_personal_basic(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        mensj = "Actualizando Personal:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                cstmt.setString("@nro_ci"           , value.getNro_ci());
                cstmt.setInt("@tpersonal"           , value.getTpersonal().getIdTPersonal());
                cstmt.setInt("@sexo"                , value.getSex().getIdSexo());
                cstmt.setString("@nombres"          , value.getNombres().toUpperCase());
                cstmt.setString("@apellidos"        , value.getApellidos().toUpperCase());
                cstmt.setInt("@id_ciudad"           , value.getCity().getIdPoblacion());
                cstmt.setInt("@id_parroquia"        , value.getParish().getIdPoblacion());
                cstmt.setInt("@id_municipio"        , value.getMunicipality().getIdPoblacion());
                cstmt.setInt("@id_estado"           , value.getState().getIdPoblacion());
                cstmt.setInt("@id_pais"             , value.getCountry());
                cstmt.setString("@direccion"        , value.getDireccion().toLowerCase());
                cstmt.setString("@telefonos"        , value.getTelefonos());
                cstmt.setString("@celular"          , value.getCelular());
                if (value.getCorreo() != null)
                    cstmt.setString("@correo"       , value.getCorreo().toLowerCase());
                else
                    cstmt.setString("@correo"       , value.getCorreo());

                if (value.getRuta_ci() != null)
                    cstmt.setString("@ruta_ci"          , value.getRuta_ci().toLowerCase());
                else
                    cstmt.setString("@ruta_ci"          , value.getRuta_ci());

                if (value.getRuta_lc() != null)
                    cstmt.setString("@ruta_lc"          , value.getRuta_lc().toLowerCase());
                else
                    cstmt.setString("@ruta_lc"          , value.getRuta_lc());

                if (value.getRuta_cm() != null)
                    cstmt.setString("@ruta_cm"          , value.getRuta_cm().toLowerCase());
                else
                    cstmt.setString("@ruta_cm"          , value.getRuta_cm());

                if (value.getRuta_cs() != null)
                    cstmt.setString("@ruta_cs"          , value.getRuta_cs().toLowerCase());
                else
                    cstmt.setString("@ruta_cs"          , value.getRuta_cs());

                if (value.getRuta_ma() != null)
                    cstmt.setString("@ruta_ma"          , value.getRuta_ma().toLowerCase());
                else
                    cstmt.setString("@ruta_ma"          , value.getRuta_ma());

                if(value.getFec_lc().toString().equals(LocalDate.now().toString())) 
                    cstmt.setDate("@fec_lc"         , null);
                else
                    cstmt.setDate("@fec_lc"         , value.getFec_lc());

                if(value.getFec_cm().toString().equals(LocalDate.now().toString())) 
                    cstmt.setDate("@fec_cm"         , null);
                else
                    cstmt.setDate("@fec_cm"         , value.getFec_cm());

                if(value.getFec_cs().toString().equals(LocalDate.now().toString())) 
                    cstmt.setDate("@fec_cs"         , null);
                else
                    cstmt.setDate("@fec_cs"         , value.getFec_cs());

                if(value.getFec_ma().toString().equals(LocalDate.now().toString())) 
                    cstmt.setDate("@fec_ma"         , null);
                else
                    cstmt.setDate("@fec_ma"         , value.getFec_ma());

                cstmt.execute();            
                // Auditar el proceso
                auditar(user,value.getNro_ci()+ "",mensj);
                connection.commit();
                return true; 
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }
    /**
     * @author MITM
     * @param user
     * @param operacion Determina el Proceso que se va a ejecutar 1 INSERT, 2 UPDATE
     * @param value
     * @return
     * @throws SQLException
     * @throws BdException 
     */
    public boolean saveTPersonal(String user,int operacion,Log_TPersonal value) throws SQLException, BdException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open(); 
            if (connection != null){
                //Inicia Transaccion 
                connection.setAutoCommit(false);
                StringBuilder sql = new StringBuilder();            
                mensj = "";
                switch(operacion){
                    case 1:
                        sql.append("{call sp_ins_log_tpersonal(?, ?)}");
                        mensj = "Nuevo Tipo Personal:";
                        break;
                    case 2:
                        sql.append("{call sp_upd_log_tpersonal_basic(?, ?, ?)}");
                        mensj = "Actualizando Tipo Personal:";
                        break;
                }
                CallableStatement cstmt = connection.prepareCall(sql.toString());
                switch(operacion){
                    case 1:
                        cstmt.setString("@nombre"       , value.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , value.getAbrev().toUpperCase());
                        break;
                    case 2:
                        cstmt.setInt("@grp_supplier"    , value.getIdTPersonal());
                        cstmt.setString("@nombre"       , value.getNombre().toUpperCase());
                        cstmt.setString("@abrev"        , value.getAbrev().toUpperCase());
                        break;
                }
                cstmt.execute();            
                // Auditar el proceso
                auditar(user,value.getIdTPersonal()+ "",mensj);
                connection.commit();
                return true; 
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(SQLException e){            
            connection.rollback();            
            throw new BdException("Error en BD: "+e);           
        }finally{
            connection.close();
        }
    }      
    /**
     * @author MITM
     * @param value
     * @param param1
     * @param param2
     * @return 
     * @throws java.sql.SQLException 
     * @throws Interfaces.BdException 
     */
    public StaffInterface[] printPersonal(StaffInterface value,String param1, String param2) throws SQLException, BdException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append("{call sp_get_log_personal_print(?,?)}");
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                cstmt.setString("@param1"  , param1 );
                cstmt.setString("@param2"  , param2 );
                ResultSet result = cstmt.executeQuery();            
                List<StaffInterface> list = new ArrayList<>();
                while(result.next()) {
                   StaffInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new StaffInterface[list.size()]); 
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /***************************************************************************/
    /******************************* SINGLETON *********************************/
    /***************************************************************************/    
    private static class BdHolder {
        private static final BdStaff INSTANCE = new BdStaff();
    }
    public static BdStaff getInstance() {
        return BdStaff.BdHolder.INSTANCE;
    }
}
